﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MixScript.BLL;

namespace MixScript.BLL.FI
{
    public class cCOA : cBaseBLL
    {

        public cCOA()
        {

        }

        public int getParent(int idcoa)
        {
            try
            {

                return (int)Dbserver.ExecuteScalar("select parentid from tb_coamst where idcoa=" + idcoa);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }


        public int addCoa(Int64 idcoa, string coaname, string coatyp, Int64 parentid, Int64 process, Int64 app, Int64 subapp)
        {
            string sSQL = "";
            string mss = "";
            if (ServerProvider.ToUpper() == "MYSQL")
            {
                sSQL = "select * from tb_coamst limit 0";
            }
            else
            {
                sSQL = "select top 0 * from tb_coamst ";
            }
            DataTable dt = new DataTable();
            dt = Dbserver.ExecuteQuery(sSQL, "tb_coamst");

            DataRow dr = dt.NewRow();

            dr["idcoa"] = idcoa;
            dr["coaname"] = coaname;
            dr["coatyp"] = coatyp;
            dr["process"] = process;
            dr["application"] = app;
            dr["subapplication"] = coatyp;
            if (parentid > 0)
            {
                if (chekParent(parentid))
                {
                    dr["parentid"] = parentid;
                }
                else
                {
                    mss = "Parent Not Found";
                    Msg = mss;
                    return 11;
                }

            }
            //dr["idcreated"] = new cApplSession().userID;
            // dr["dtcreated"] = getServerTime();
            // dr["dtupdated"] = dr["dtcreated"];


            dt.Rows.Add(dr);
            int j = 0;
            if (Dbserver.openConn())
            {
                j = Dbserver.SqlUpdate(dt, "tb_coamst", out mss);

                if (j == 1)
                {
                    mss = "Sukses";
                    Msg = mss;
                    Dbserver.CommitAndCloseConn();
                    return 0;
                }
                else
                {
                    return 12;
                }
            }
            else
            {
                mss = "Database unreachable";
                Msg = mss;
                return 13;
            }

        }
        public bool chekParent(string parentid)
        {
            Int64 p = 0;
            Int64.TryParse(parentid, out p);
            if (parentid.Trim() == string.Empty)
            {
                return true;
            }
            else
            {
                return chekParent(p);
            }
        }
        public bool haveChild(Int64 parentid)
        {
            DataTable dt = new DataTable();
            return getCoa(ref dt, 0, "", "", parentid);

        }
        public bool chekParent(Int64 parentid)
        {
            DataTable dt = new DataTable();
            return getCoa(ref dt, parentid, "", "", 0);

        }


        public bool getCoa(ref DataTable dt, Int64 idcoa, string coaname, string coatyp, Int64 parentid)
        {
            try
            {

                dt = new DataTable();
                string sSQL = @"select * FROM  vw_coamst vw where 1=1 AND len(vw.idcoa)  <= 3   ";
                if (idcoa > 0)
                {
                    sSQL += "and vw .idcoa=" + idcoa.ToString() + " ";
                }

                if (coaname.Trim() != string.Empty)
                {
                    sSQL += "and vw.coaname like '%" + coaname + "%' ";
                }
                if (coatyp.Trim() != string.Empty)
                {
                    sSQL += "and vw.coatyp='" + coatyp + "' ";
                }
                if (parentid > 0)
                {
                    sSQL += "and vw.parentid=" + parentid + " ";
                }

                sSQL += "order by vw.idcoa";

                dt = Dbserver.ExecuteQuery(sSQL, "vw_coamst");


                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public bool getCoa(ref DataTable dt, Int64 coaid)
        {
            try
            {
                dt = new DataTable();
                string sSQL = @"select * from vw_coamst where 1=1 and idcoa = '{0}'";
                dt = Dbserver.ExecuteQuery(string.Format(sSQL, coaid), "vw_coamst");
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public bool getSubCoa(ref DataTable dt, Int64 parentid)
        {
            try
            {

                dt = new DataTable();
                string sSQL = @"SELECT acctnum,acctname,dbcd,type,
                        replace(substring(acctnum,1,(LEN(acctnum)-2)),'.','') as level,
                        CASE process 
                        WHEN 1 THEN 'None'
                        WHEN 2 THEN 'Cash'
                        WHEN 3 THEN 'Bank'
                        WHEN 4 THEN 'A/R Customer'
                        WHEN 5 THEN 'A/R Credit Card'
                        WHEN 6 THEN 'A/R Employee'
                        WHEN 7 THEN 'A/P Supplier'
                        WHEN 8 THEN 'Vehical'
                        WHEN 9 THEN 'Raw Material'
                        WHEN 10 THEN 'Sub Material'
                        WHEN 11 THEN 'Work in Process'
                        WHEN 12 THEN 'Finished Good'
                        END process,
                        CASE aplication 
                        WHEN 1 THEN 'ASSET'
                        WHEN 2 THEN 'Liabilities'
                        WHEN 3 THEN 'Capital'
                        WHEN 4 THEN 'Revenue'
                        WHEN 5 THEN 'Expense'
                        WHEN 6 THEN 'Other Revenue'
                        WHEN 7 THEN 'Other Expense'
                        END aplication , process processID,aplication aplicationID 
                        FROM accaccounts 
                        where left(acctnum,3) = '{0}'
                            Order by [acctnum]";

                dt = Dbserver.ExecuteQuery(string.Format(sSQL, parentid), "vw_coamst");


                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public bool getCoaDetail(ref DataTable dt, string id)
        {
            try
            {

                dt = new DataTable();
                string sSQL = @"SELECT acctnum,acctname,dbcd,type,
                        replace(substring(acctnum,1,(LEN(acctnum)-2)),'.','') as level,
                        CASE process 
                        WHEN 1 THEN 'None'
                        WHEN 2 THEN 'Cash'
                        WHEN 3 THEN 'Bank'
                        WHEN 4 THEN 'A/R Customer'
                        WHEN 5 THEN 'A/R Credit Card'
                        WHEN 6 THEN 'A/R Employee'
                        WHEN 7 THEN 'A/P Supplier'
                        WHEN 8 THEN 'Vehical'
                        WHEN 9 THEN 'Raw Material'
                        WHEN 10 THEN 'Sub Material'
                        WHEN 11 THEN 'Work in Process'
                        WHEN 12 THEN 'Finished Good'
                        END process,
                        CASE aplication 
                        WHEN 1 THEN 'ASSET'
                        WHEN 2 THEN 'Liabilities'
                        WHEN 3 THEN 'Capital'
                        WHEN 4 THEN 'Revenue'
                        WHEN 5 THEN 'Expense'
                        WHEN 6 THEN 'Other Revenue'
                        WHEN 7 THEN 'Other Expense'
                        END aplication , process processID,aplication aplicationID,subaplication  
                        FROM accaccounts 
                        where acctnum = '{0}'
                            Order by [acctnum]";

                dt = Dbserver.ExecuteQuery(string.Format(sSQL, id), "vw_coamst");


                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public bool getSubCoaWithType(ref DataTable dt, Int64 parentid, Int64 type)
        {
            try
            {

                dt = new DataTable();
                string sSQL = @"select CASE Len(CAST(idcoa AS NVARCHAR(20))) 
							WHEN 8 THEN CAST(idcoa AS NVARCHAR(20)) + '0'
                            WHEN 7 THEN CAST(idcoa AS NVARCHAR(20)) + '00'
                            WHEN 6 THEN CAST(idcoa AS NVARCHAR(20)) + '000' 
                            WHEN 5 THEN CAST(idcoa AS NVARCHAR(20)) + '0000' 
                            WHEN 4 THEN CAST(idcoa AS NVARCHAR(20)) + '00000' 
                            End [NewID] , 'M' [Root],[idcoa]
                            ,[coaname],CASE [coatyp] WHEN 'D' THEN 'Debit' ELSE 'Credit' END coatyp,[parentid],[application_name],[Process_name]
                            ,[subapplication_name],[process],[application]
                            ,[subapplication] FROM  vw_coamst where 1=1 and parentid 
                            = '{0}' And process = '{1}'
                            UNION 
                            select CASE Len(CAST(idcoa AS NVARCHAR(20))) 
							WHEN 8 THEN CAST(idcoa AS NVARCHAR(20)) + '0'
                            WHEN 7 THEN CAST(idcoa AS NVARCHAR(20)) + '00'
                            WHEN 6 THEN CAST(idcoa AS NVARCHAR(20)) + '000' 
                            WHEN 5 THEN CAST(idcoa AS NVARCHAR(20)) + '0000' 
                            WHEN 4 THEN CAST(idcoa AS NVARCHAR(20)) + '00000' 
                            End [NewID] ,'C' [Root],[idcoa],'>> ' + [coaname]
                            ,CASE [coatyp] WHEN 'D' THEN 'Debit' ELSE 'Credit' END coatyp,[parentid],[application_name]
                            ,[Process_name],[subapplication_name]
                            ,[process],[application]
                            ,[subapplication] FROM  vw_coamst where 1=1 and parentid 
                            IN (select idcoa  FROM  vw_coamst where 1=1 and parentid 
                            = '{0}' And process = '{1}') And process = '{1}'
                            Order by [NewID]";

                dt = Dbserver.ExecuteQuery(string.Format(sSQL, parentid, type), "vw_coamst");


                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public bool SaveCOA(string acctnum, string acctname, Int32 aplication, Int32 subaplication, string currency, string dbcd,
                    Int32 process, Int32 tipe, Int32 groupnum, Int32 subnum, bool isNew)
        {

            string Sql;
            string sSQl;
            if (isNew)
            {
                Sql = @"INSERT INTO accaccounts (acctnum,acctcust,acctname,aplication,subaplication,currency,dbcd,process,type,groupnum,subnum,center,usedmat,project,budget,finan_1,finan_2,finan_3) 
	                VALUES ('{0}','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',0,0,0,0,0,0,0)";
                sSQl = string.Format(Sql, acctnum, acctname, aplication, subaplication, currency, dbcd,
                                    process, tipe, groupnum, subnum);
            }
            else
            {
                Sql = @"UPDATE accaccounts SET acctname = '{1}',
			        aplication = '{2}', subaplication = '{3}',currency = '{4}',
			        dbcd = '{5}',process = '{6}',type = '{7}' WHERE acctnum LIKE '{0}'";
                sSQl = string.Format(Sql, acctnum, acctname, aplication, subaplication, currency, dbcd,
                                        process, tipe);
            }
            try
            {
                Dbserver.ExecuteScalar(sSQl);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteCOA(string acctnum)
        {

            string Sql;
            string sSQl;
            Sql = @"DELETE accaccounts WHERE acctnum = '{0}'";
            sSQl = string.Format(Sql, acctnum);
            try
            {
                Dbserver.ExecuteScalar(sSQl);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }





    }
}
